package za.co.smartmi.dao.impl;

import za.co.smartmi.dao.PolicylineDao;
import za.co.smartmi.model.Policyline;
import za.co.smartmi.model.PolicylinePk;
import za.co.smartmi.dao.exceptions.PolicylineDaoException;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import za.co.smartmi.dao.support.JdbcGenericDao;

@Repository
public class PolicylineDaoImpl extends JdbcGenericDao implements ParameterizedRowMapper<Policyline>, PolicylineDao
{
    
        @Autowired
        public void setDataSource(DataSource dataSource) {
                setJdbcTemplate(new JdbcTemplate(dataSource));
	}

	/**
	 * Method 'insert'
	 * 
	 * @param dto
	 * @return PolicylinePk
	 */
	@Transactional
	public PolicylinePk insert(Policyline dto)
	{
		getJdbcTemplate().update("INSERT INTO " + getTableName() + " ( policylineid, premium, status, startdate, enddate, csys, cdate, usys, udate, policyid, clientid, productid, beneficiaryid, repid ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )",dto.getPolicylineid(),dto.getPremium(),dto.getStatus(),dto.getStartdate(),dto.getEnddate(),dto.getCsys(),dto.getCdate(),dto.getUsys(),dto.getUdate(),dto.getPolicyid(),dto.getClientid(),dto.getProductid(),dto.getBeneficiaryid(),dto.getRepid());
		return dto.createPk();
	}

	/** 
	 * Updates a single row in the policyline table.
	 */
	@Transactional
	public void update(PolicylinePk pk, Policyline dto) throws PolicylineDaoException
	{
		getJdbcTemplate().update("UPDATE " + getTableName() + " SET policylineid = ?, premium = ?, status = ?, startdate = ?, enddate = ?, csys = ?, cdate = ?, usys = ?, udate = ?, policyid = ?, clientid = ?, productid = ?, beneficiaryid = ?, repid = ? WHERE policyid = ? AND clientid = ? AND productid = ?",dto.getPolicylineid(),dto.getPremium(),dto.getStatus(),dto.getStartdate(),dto.getEnddate(),dto.getCsys(),dto.getCdate(),dto.getUsys(),dto.getUdate(),dto.getPolicyid(),dto.getClientid(),dto.getProductid(),dto.getBeneficiaryid(),dto.getRepid(),pk.getPolicyid(),pk.getClientid(),pk.getProductid());
	}

	/** 
	 * Deletes a single row in the policyline table.
	 */
	@Transactional
	public void delete(PolicylinePk pk) throws PolicylineDaoException
	{
		getJdbcTemplate().update("DELETE FROM " + getTableName() + " WHERE policyid = ? AND clientid = ? AND productid = ?",pk.getPolicyid(),pk.getClientid(),pk.getProductid());
	}

	/**
	 * Method 'mapRow'
	 * 
	 * @param rs
	 * @param row
	 * @throws SQLException
	 * @return Policyline
	 */
	public Policyline mapRow(ResultSet rs, int row) throws SQLException
	{
		Policyline dto = new Policyline();
		dto.setPolicylineid( new Integer( rs.getInt(1) ) );
		if (rs.wasNull()) {
			dto.setPolicylineid( null );
		}
		
		dto.setPremium( rs.getBigDecimal(2));
		dto.setStatus( rs.getString( 3 ) );
		dto.setStartdate( rs.getDate(4 ) );
		dto.setEnddate( rs.getDate(5 ) );
		dto.setCsys( rs.getString( 6 ) );
		dto.setCdate( rs.getDate(7 ) );
		dto.setUsys( rs.getString( 8 ) );
		dto.setUdate( rs.getDate(9 ) );
		dto.setPolicyid( new Integer( rs.getInt(10) ) );
		dto.setClientid( new Integer( rs.getInt(11) ) );
		dto.setProductid( new Integer( rs.getInt(12) ) );
		dto.setBeneficiaryid( new Integer( rs.getInt(13) ) );
		if (rs.wasNull()) {
			dto.setBeneficiaryid( null );
		}
		
		dto.setRepid( new Integer( rs.getInt(14) ) );
		if (rs.wasNull()) {
			dto.setRepid( null );
		}
		
		return dto;
	}

	/**
	 * Method 'getTableName'
	 * 
	 * @return String
	 */
	public String getTableName()
	{
		return "smartMI.policyline";
	}

	/** 
	 * Returns all rows from the policyline table that match the criteria 'policyid = :policyid AND clientid = :clientid AND productid = :productid'.
	 */
	@Transactional
	public Policyline findByPrimaryKey(Integer policyid, Integer clientid, Integer productid) throws PolicylineDaoException
	{
		try {
			List<Policyline> list = getJdbcTemplate().query("SELECT policylineid, premium, status, startdate, enddate, csys, cdate, usys, udate, policyid, clientid, productid, beneficiaryid, repid FROM " + getTableName() + " WHERE policyid = ? AND clientid = ? AND productid = ?", this,policyid,clientid,productid);
			return list.size() == 0 ? null : list.get(0);
		}
		catch (Exception e) {
			throw new PolicylineDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the policyline table that match the criteria ''.
	 */
	@Transactional
	public List<Policyline> findAll() throws PolicylineDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT policylineid, premium, status, startdate, enddate, csys, cdate, usys, udate, policyid, clientid, productid, beneficiaryid, repid FROM " + getTableName() + " ORDER BY policyid, clientid, productid", this);
		}
		catch (Exception e) {
			throw new PolicylineDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the policyline table that match the criteria 'policylineid = :policylineid'.
	 */
	@Transactional
	public List<Policyline> findWherePolicylineidEquals(Integer policylineid) throws PolicylineDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT policylineid, premium, status, startdate, enddate, csys, cdate, usys, udate, policyid, clientid, productid, beneficiaryid, repid FROM " + getTableName() + " WHERE policylineid = ? ORDER BY policylineid", this,policylineid);
		}
		catch (Exception e) {
			throw new PolicylineDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the policyline table that match the criteria 'premium = :premium'.
	 */
	@Transactional
	public List<Policyline> findWherePremiumEquals(BigDecimal premium) throws PolicylineDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT policylineid, premium, status, startdate, enddate, csys, cdate, usys, udate, policyid, clientid, productid, beneficiaryid, repid FROM " + getTableName() + " WHERE premium = ? ORDER BY premium", this,premium);
		}
		catch (Exception e) {
			throw new PolicylineDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the policyline table that match the criteria 'status = :status'.
	 */
	@Transactional
	public List<Policyline> findWhereStatusEquals(String status) throws PolicylineDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT policylineid, premium, status, startdate, enddate, csys, cdate, usys, udate, policyid, clientid, productid, beneficiaryid, repid FROM " + getTableName() + " WHERE status = ? ORDER BY status", this,status);
		}
		catch (Exception e) {
			throw new PolicylineDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the policyline table that match the criteria 'startdate = :startdate'.
	 */
	@Transactional
	public List<Policyline> findWhereStartdateEquals(Date startdate) throws PolicylineDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT policylineid, premium, status, startdate, enddate, csys, cdate, usys, udate, policyid, clientid, productid, beneficiaryid, repid FROM " + getTableName() + " WHERE startdate = ? ORDER BY startdate", this,startdate);
		}
		catch (Exception e) {
			throw new PolicylineDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the policyline table that match the criteria 'enddate = :enddate'.
	 */
	@Transactional
	public List<Policyline> findWhereEnddateEquals(Date enddate) throws PolicylineDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT policylineid, premium, status, startdate, enddate, csys, cdate, usys, udate, policyid, clientid, productid, beneficiaryid, repid FROM " + getTableName() + " WHERE enddate = ? ORDER BY enddate", this,enddate);
		}
		catch (Exception e) {
			throw new PolicylineDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the policyline table that match the criteria 'csys = :csys'.
	 */
	@Transactional
	public List<Policyline> findWhereCsysEquals(String csys) throws PolicylineDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT policylineid, premium, status, startdate, enddate, csys, cdate, usys, udate, policyid, clientid, productid, beneficiaryid, repid FROM " + getTableName() + " WHERE csys = ? ORDER BY csys", this,csys);
		}
		catch (Exception e) {
			throw new PolicylineDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the policyline table that match the criteria 'cdate = :cdate'.
	 */
	@Transactional
	public List<Policyline> findWhereCdateEquals(Date cdate) throws PolicylineDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT policylineid, premium, status, startdate, enddate, csys, cdate, usys, udate, policyid, clientid, productid, beneficiaryid, repid FROM " + getTableName() + " WHERE cdate = ? ORDER BY cdate", this,cdate);
		}
		catch (Exception e) {
			throw new PolicylineDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the policyline table that match the criteria 'usys = :usys'.
	 */
	@Transactional
	public List<Policyline> findWhereUsysEquals(String usys) throws PolicylineDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT policylineid, premium, status, startdate, enddate, csys, cdate, usys, udate, policyid, clientid, productid, beneficiaryid, repid FROM " + getTableName() + " WHERE usys = ? ORDER BY usys", this,usys);
		}
		catch (Exception e) {
			throw new PolicylineDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the policyline table that match the criteria 'udate = :udate'.
	 */
	@Transactional
	public List<Policyline> findWhereUdateEquals(Date udate) throws PolicylineDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT policylineid, premium, status, startdate, enddate, csys, cdate, usys, udate, policyid, clientid, productid, beneficiaryid, repid FROM " + getTableName() + " WHERE udate = ? ORDER BY udate", this,udate);
		}
		catch (Exception e) {
			throw new PolicylineDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the policyline table that match the criteria 'policyid = :policyid'.
	 */
	@Transactional
	public List<Policyline> findWherePolicyidEquals(Integer policyid) throws PolicylineDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT policylineid, premium, status, startdate, enddate, csys, cdate, usys, udate, policyid, clientid, productid, beneficiaryid, repid FROM " + getTableName() + " WHERE policyid = ? ORDER BY policyid", this,policyid);
		}
		catch (Exception e) {
			throw new PolicylineDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the policyline table that match the criteria 'clientid = :clientid'.
	 */
	@Transactional
	public List<Policyline> findWhereClientidEquals(Integer clientid) throws PolicylineDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT policylineid, premium, status, startdate, enddate, csys, cdate, usys, udate, policyid, clientid, productid, beneficiaryid, repid FROM " + getTableName() + " WHERE clientid = ? ORDER BY clientid", this,clientid);
		}
		catch (Exception e) {
			throw new PolicylineDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the policyline table that match the criteria 'productid = :productid'.
	 */
	@Transactional
	public List<Policyline> findWhereProductidEquals(Integer productid) throws PolicylineDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT policylineid, premium, status, startdate, enddate, csys, cdate, usys, udate, policyid, clientid, productid, beneficiaryid, repid FROM " + getTableName() + " WHERE productid = ? ORDER BY productid", this,productid);
		}
		catch (Exception e) {
			throw new PolicylineDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the policyline table that match the criteria 'beneficiaryid = :beneficiaryid'.
	 */
	@Transactional
	public List<Policyline> findWhereBeneficiaryidEquals(Integer beneficiaryid) throws PolicylineDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT policylineid, premium, status, startdate, enddate, csys, cdate, usys, udate, policyid, clientid, productid, beneficiaryid, repid FROM " + getTableName() + " WHERE beneficiaryid = ? ORDER BY beneficiaryid", this,beneficiaryid);
		}
		catch (Exception e) {
			throw new PolicylineDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the policyline table that match the criteria 'repid = :repid'.
	 */
	@Transactional
	public List<Policyline> findWhereRepidEquals(Integer repid) throws PolicylineDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT policylineid, premium, status, startdate, enddate, csys, cdate, usys, udate, policyid, clientid, productid, beneficiaryid, repid FROM " + getTableName() + " WHERE repid = ? ORDER BY repid", this,repid);
		}
		catch (Exception e) {
			throw new PolicylineDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns the rows from the policyline table that matches the specified primary-key value.
	 */
	public Policyline findByPrimaryKey(PolicylinePk pk) throws PolicylineDaoException
	{
		return findByPrimaryKey( pk.getPolicyid(), pk.getClientid(), pk.getProductid() );
	}

}
